package cn.edu.tju.spider;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DataConTool {
	
	public static final String driver = "com.mysql.jdbc.Driver";
	public static final String url = "jdbc:mysql://192.168.1.109/my_wikidb?useUnicode=true&characterEncoding=UTF-8";//UTF-8";
	public static final String user = "root";
	public static final String password = "1234";
	public static Connection conn = null;
	static{
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public static List<Map<String, Object>> getAttribute(String sqlStr, String[] keyset) throws ClassNotFoundException{
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Statement stat = null;
			ResultSet rs = null;
			List<Map<String, Object>> resultMap = new ArrayList<Map<String, Object>>();
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				while(rs.next()) {
					Map<String, Object> itemMap = new HashMap<String, Object>();
					for (int i = 0; i < keyset.length; i++) {
//						System.out.println("rs.next() = "+rs.getString(keyset[i]));
						itemMap.put(keyset[i], rs.getString(keyset[i]));
					}
					resultMap.add(itemMap);
				}
				return resultMap;
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				stat.close();
				if(rs!=null){
					rs.close();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return null;
	}
	
	public static void insertSQL(String scheme, String[] keyset, Map<String, Object> itemData) throws ClassNotFoundException{
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Statement stat = null;
			ResultSet rs = null;
			String sqlStr = "insert into "+scheme + "(";
			for (int i = 0; i < keyset.length-1; i++) {
				sqlStr += keyset[i] + ",";
			}
			sqlStr += keyset[keyset.length-1] + ") values(";
			for (int i = 0; i < keyset.length-1; i++) {
				sqlStr += "'"+ itemData.get(keyset[i]) + "',";
			}
			sqlStr += "'"+ itemData.get(keyset[keyset.length-1]) + "');";
			System.out.println(sqlStr);
			try{
				stat = conn.createStatement();
				stat.execute(sqlStr);
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if(rs!=null){
					rs.close();
				}
				stat.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	public static void updateSql(String scheme, String sqlStr) throws ClassNotFoundException{
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Connection conn = DriverManager.getConnection(url, user, password);
			Statement stat = null;
			try{
				stat = conn.createStatement();
				stat.executeUpdate(sqlStr);
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				stat.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
}
